package com.mbbmap.map;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.TreeMap;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import com.classes.CasemastFactory;
import com.mbbmap.app.home.SecUsersHome;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.security.action.CommonDispatchAction;
import com.mbbmap.security.dao.SecAccessDao;
import com.mbbmap.security.dao.SecGroupsDao;
import com.mbbmap.security.dao.SecUserDao;
import com.mbbmap.util.Constants;

public class VisaVMTSBatchAction extends CommonDispatchAction {
	private int iApp9 = 0; //APP009
	private static final String CONFIG_FILE = "/system.properties";
	private static final java.util.Properties appCfg = new java.util.Properties();
	
	private boolean authorized(HttpServletRequest request){
		HttpSession session = request.getSession(false);
		@SuppressWarnings("unchecked")
		ArrayList<SecAccessDao> secGroupAccessList = (ArrayList<SecAccessDao>) session.getAttribute(Constants.LOGON_ACCESS_LIST);
		
		this.iApp9 = 0;
		
		for(int i=0;i<secGroupAccessList.size();i++){
			SecAccessDao secaccessDao = (SecAccessDao)secGroupAccessList.get(i);
			String strModuleCode  = secaccessDao.getModuleCode();
			if(strModuleCode.equals("APP009")){
				this.iApp9 = 1;
			}
		}
		
		if(this.iApp9 > 0) {
			return true;
		}
		
		return false;
	}
	
	public ActionForward init(ActionMapping mapping,
		ActionForm form, HttpServletRequest request,
		HttpServletResponse response) throws Exception {

		final String PROG_ID="VisaVMTSBatchAction.init";

		System.out.println(PROG_ID + " : BEGIN");
		HttpSession session = request.getSession(false);
		
		String nextPage = null;
		
		//check session condition
		if (session == null){
			return mapping.findForward(Constants.KEY_ACTION_SESSION_ERR);
		}
//		if(!isTokenValid(request)){
//			System.out.println("Token validation failed!");
//			session.invalidate();
//			return mapping.findForward(Constants.KEY_ACTION_SESSION_ERR);
//		}else{
//			resetToken(request);
//			System.out.println("Token validation passed!");
//		}

		// browser javasript is not enable
		if (request.getParameter(Constants.PARAM_JS_ENABLE) != null && request.getParameter(Constants.PARAM_JS_ENABLE).equals("no") ) {
			String actionMsg = ConfigManager.getInstance().get(Constants.MSG_ERROR_JAVASCRIPT_NOT_ENABLE);
			session.setAttribute(Constants.LOGON_ERR_MSG, actionMsg);
			if (DEBUG) System.out.println("... Javascript not enable");
			return mapping.findForward(Constants.KEY_ACTION_MAPPING_NOSCRIPT);
		}
		String sessionstatus = (String) session.getAttribute(Constants.VISIT_STATUS);
		SecUserDao oSecUserDao = (SecUserDao) session.getAttribute(Constants.LOGGED_USER);// read user session time
		
		String strFwd = "";
		if (oSecUserDao == null){
			strFwd ="sessionError";
		}
		else
		{
			
		}
		if (sessionstatus != null){
			if (DEBUG) System.out.println("sessionstatus !=null");
			if ((sessionstatus.equals(Constants.VISIT_STATUS_VISITED)) || (session.isNew())){
				//This is from refresh button.. set session expires.
				if (DEBUG) System.out.println("sessionstatus !=null 2");
				String errMsg = ConfigManager.getInstance().get(Constants.MSG_LOGON_ERR_LOGOUT_ACTIVE_SESSION);
				session.setAttribute(Constants.LOGON_ERR_MSG, errMsg);
				return mapping.findForward(Constants.KEY_ACTION_LOGOUT);

			}
		}
		else if (oSecUserDao == null){// if the session expire, it will bring user to timeout screen
			
			return mapping.findForward(strFwd);
	
		}
		
		
		/********************************************************************************/
		/* Custom code starts here */
		
		nextPage="visa_vmts_batch";
		if(this.authorized(request) == false){
			nextPage="not_authorized";
		}
		
		
		String strEMerchant = (String) session.getAttribute(Constants.MERCHANT_KEY);
		
		TreeMap<String, String> search = new TreeMap<String, String>();
		
		if(request.getParameter("search_submit") != null){
			if(request.getParameter("case_id") != null){
				search.put("case_id", sanitize(request.getParameter("case_id")));
			}
			
			if(request.getParameter("merchant_name") != null){
				search.put("merchant_name", sanitize(request.getParameter("merchant_name")));
			}
			
			if(request.getParameter("group_code") != null){
				search.put("group_code", sanitize(request.getParameter("group_code")));
			}
			
			if(request.getParameter("group_user") != null){
				search.put("group_user", sanitize(request.getParameter("group_user")));
			}
			
			if(request.getParameter("application_type") != null){
				search.put("application_type", sanitize(request.getParameter("application_type")));
			}
			
			if(request.getParameter("reseller_location") != null){
				search.put("reseller_location", sanitize(request.getParameter("reseller_location")));
			}
			
			if(request.getParameter("status") != null){
				search.put("status", sanitizePwd(request.getParameter("status")));
			}
		}
		
		ArrayList<TreeMap<String, String>> cArr = CasemastFactory.getPendingVisaVMTSCasemasts(search);
		if(cArr == null){
			json_error(CasemastFactory.getMessage(), response);
			return null;
		}
		
		
		request.setAttribute("cArr", cArr);
		
		
		if(request.getParameter("export_submit") != null){
			this.downloadExcelFile(response, cArr, oSecUserDao.getLogonId());
			return null;
		}
		
		
		request.setAttribute("search", search);
		
		
		ArrayList<String> statusList = CasemastFactory.getStatusFilter(1);
		if(statusList == null){
			json_error(CasemastFactory.getMessage(), response);
			return null;
		}
		
		request.setAttribute("statusList", statusList);
		
		ArrayList<SecGroupsDao> groupList = this.myGroupList(request);
		request.setAttribute("groupList", groupList);
		
		ArrayList<SecUserDao> userList = new ArrayList<SecUserDao>();
		if ((strEMerchant != null) && (!strEMerchant.equals(""))){
			if(search.containsKey("group_code") && search.get("group_code").length() > 0){
				userList = SecUsersHome.getInstance().findSecGroupUserList(strEMerchant, search.get("group_code"));
			}
		}
		
		request.setAttribute("userList", userList);
		
		return mapping.findForward(nextPage);
	}
	
	public ActionForward batch(ActionMapping mapping,
		ActionForm form, HttpServletRequest request,
		HttpServletResponse response) throws Exception {

		final String PROG_ID="VisaVMTSBatchAction.batch";

		System.out.println(PROG_ID + " : BEGIN");
		HttpSession session = request.getSession(false);
		
		String nextPage = null;
		
		//check session condition
		if (session == null){
			return mapping.findForward(Constants.KEY_ACTION_SESSION_ERR);
		}

		// browser javasript is not enable
		if (request.getParameter(Constants.PARAM_JS_ENABLE) != null && request.getParameter(Constants.PARAM_JS_ENABLE).equals("no") ) {
			String actionMsg = ConfigManager.getInstance().get(Constants.MSG_ERROR_JAVASCRIPT_NOT_ENABLE);
			session.setAttribute(Constants.LOGON_ERR_MSG, actionMsg);
			if (DEBUG) System.out.println("... Javascript not enable");
			return mapping.findForward(Constants.KEY_ACTION_MAPPING_NOSCRIPT);
		}
		String sessionstatus = (String) session.getAttribute(Constants.VISIT_STATUS);
		SecUserDao oSecUserDao = (SecUserDao) session.getAttribute(Constants.LOGGED_USER);// read user session time
		
		String strFwd = "";
		if (oSecUserDao == null){
			strFwd ="sessionError";
		}
		else
		{
			
		}
		if (sessionstatus != null){
			if (DEBUG) System.out.println("sessionstatus !=null");
			if ((sessionstatus.equals(Constants.VISIT_STATUS_VISITED)) || (session.isNew())){
				//This is from refresh button.. set session expires.
				if (DEBUG) System.out.println("sessionstatus !=null 2");
				String errMsg = ConfigManager.getInstance().get(Constants.MSG_LOGON_ERR_LOGOUT_ACTIVE_SESSION);
				session.setAttribute(Constants.LOGON_ERR_MSG, errMsg);
				return mapping.findForward(Constants.KEY_ACTION_LOGOUT);

			}
		}
		else if (oSecUserDao == null){// if the session expire, it will bring user to timeout screen
			
			return mapping.findForward(strFwd);
	
		}
		
		
		/********************************************************************************/
		/* Custom code starts here */
		
		nextPage="visa_vmts_batch2";
		if(this.authorized(request) == false){
			nextPage="not_authorized";
		}
		
		String batch_id = "";
		if(request.getParameter("batch_id") != null) {
			batch_id = sanitize(request.getParameter("batch_id"));
		}
		
		Date d = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		request.setAttribute("todayDate", df.format(d));
		
		
		TreeMap<String, String> search = new TreeMap<String, String>();
		
		if(request.getParameter("search_submit") != null){
			if(request.getParameter("submit_date") != null){
				String paramStr = request.getParameter("submit_date");
				if(paramStr.matches("^[0-9]{4}\\-[0-9]{2}\\-[0-9]{2}$")) {
					search.put("submit_date", sanitize(request.getParameter("submit_date")));
				} else {
					search.put("submit_date", (String) request.getAttribute("todayDate"));
				}
			}
		}
		else {
			search.put("submit_date", (String) request.getAttribute("todayDate"));
		}
		
		ArrayList<TreeMap<String, String>> cArr = CasemastFactory.getVisaVMTSBatch(search);
		if(cArr == null){
			json_error(CasemastFactory.getMessage(), response);
			return null;
		}
		
		
		if(batch_id.equals("") == false){
			ArrayList<TreeMap<String, String>> cArr2 = CasemastFactory.getVisaVMTSBatchDetails(Long.parseLong(batch_id));
			if(cArr2 == null){
				json_error(CasemastFactory.getMessage(), response);
				return null;
			}
			
			request.setAttribute("cArr2", cArr2);
			request.setAttribute("batch_id", batch_id);
			
			if(request.getParameter("export_submit") != null){
				this.downloadExcelFile(response, cArr2, oSecUserDao.getLogonId());
				return null;
			}
		}
		
		
		request.setAttribute("search", search);
		request.setAttribute("cArr", cArr);
		
		return mapping.findForward(nextPage);
	}
	
	
	public void downloadExcelFile(HttpServletResponse response, ArrayList<TreeMap<String, String>> cArr, String userId) throws Exception {
		try {
			InputStream stream = VisaVMTSBatchAction.class.getResourceAsStream(CONFIG_FILE);
			if (stream != null){
				appCfg.load(stream);
			} else {
				System.out.println("VisaVMTSBatchAction Input Stream is NULL");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		
		String exportDir = appCfg.getProperty("Export_Path");
		String fileName = "visa_vmts_batch_" + sdf.format(new Date()) + "_" + userId + ".xls";
		String exportFileName = exportDir + "/" + fileName; 
		
		
		String p = this.getServlet().getServletContext().getRealPath("/resource/VMTS_Batch_File.xls");
		if (p==null || p.equalsIgnoreCase("")){
			p = "D:/Oracle/Middleware/user_projects/domains/mapsvr/servers/mapweb/tmp/_WL_user/mbbmap/cd9sqa/war/resource/VMTS_Batch_File.xls";
			System.out.println("p:"+p);
		}
		
		File aFile = new File(p);
		byte[] byteIn = new byte[(int) aFile.length()];
		
		FileInputStream fileIn = new FileInputStream(aFile);
		
		try {
			fileIn.read(byteIn);
			FileOutputStream fileOut = new FileOutputStream(new File(exportFileName));
			
			try {
				fileOut.write(byteIn);
			} finally {
				fileOut.close();
			}
		} finally {
			fileIn.close();
		}
		
		
		fileIn = new FileInputStream(exportFileName);
		HSSFWorkbook workbook;
		
		try {
			workbook  = new HSSFWorkbook(fileIn);
			HSSFSheet sheet = workbook.getSheet("Batch");
			
			int nextRow = 1;
			sdf = new SimpleDateFormat("yyyy-MM-dd");
			
			for(TreeMap<String, String> c : cArr){
				String acquirer_country_code = "&nbsp;";
				String merchant_country_code = "";
				String contract_open_date = sdf.format(new Date());
				String contract_close_date = sdf.format(new Date());
				
				if(c.get("country_code") != null) {
					if(c.get("country_code").equals("PH")){
						acquirer_country_code = "608";
						merchant_country_code = "608";
					} else if(c.get("country_code").equals("SG")){
						acquirer_country_code = "702";
						merchant_country_code = "702";
					} else if(c.get("country_code").equals("MY")){
						acquirer_country_code = "458";
						merchant_country_code = "458";
					}
				}
				
				
				String line_1 = "";
				String line_2 = "";
				String line_3 = "";
				String line_4 = "";
				String postal_code = "";
				String city = "";
				String region_code = "";
				
				if(c.get("address") != null && c.get("address").length() > 0){
					JSONObject obj = (JSONObject) JSONValue.parse(c.get("address"));
					
					if(obj != null) {
						line_1 = (obj.get("line_1") == null) ? "" : obj.get("line_1").toString().trim();
						line_2 = (obj.get("line_2") == null) ? "" : obj.get("line_2").toString().trim();
						line_3 = (obj.get("line_3") == null) ? "" : obj.get("line_3").toString().trim();
						line_4 = (obj.get("line_4") == null) ? "" : obj.get("line_4").toString().trim();
						postal_code = (obj.get("postal_code") == null) ? "" : obj.get("postal_code").toString().trim();
						city = (obj.get("city") == null) ? "" : obj.get("city").toString().trim();
						region_code = (obj.get("region_code") == null) ? "" : obj.get("region_code").toString().trim();
						
						if(region_code.equals("002")) {
							region_code = "Sabah";
						} else if(region_code.equals("003")) {
							region_code = "Sarawak";
						} else if(region_code.equals("004")) {
							region_code = "Perlis";
						} else if(region_code.equals("005")) {
							region_code = "Kedah";
						} else if(region_code.equals("006")) {
							region_code = "Pulau Pinang";
						} else if(region_code.equals("007")) {
							region_code = "Perak";
						} else if(region_code.equals("008")) {
							region_code = "Kelantan";
						} else if(region_code.equals("009")) {
							region_code = "Terengganu";
						} else if(region_code.equals("010")) {
							region_code = "Pahang";
						} else if(region_code.equals("011")) {
							region_code = "Selangor";
						} else if(region_code.equals("012")) {
							region_code = "Negeri Sembilan";
						} else if(region_code.equals("013")) {
							region_code = "Melaka";
						} else if(region_code.equals("014")) {
							region_code = "Johor";
						} else if(region_code.equals("015")) {
							region_code = "Wilayah Persekutuan";
						}
						
					} 
				}
				
				
				String owner_name_1 = "";
				String owner_id_1 = "";
				String owner_contact_1 = "";
				String owner_email_1 = "";
				String owner_name_2 = "";
				String owner_id_2 = "";
				String owner_contact_2 = "";
				String owner_email_2 = "";
				String owner_name_3 = "";
				String owner_id_3 = "";
				String owner_contact_3 = "";
				String owner_email_3 = "";
				
				if(c.get("match_trace") != null && c.get("match_trace").length() > 0){
					JSONArray arr = (JSONArray) JSONValue.parse(c.get("match_trace"));
					
					for(int k = 0; k < arr.size(); k++) {
						String string = arr.get(k).toString();
				    	JSONObject obj = (JSONObject)JSONValue.parse(string);
				    	
						String f_name = (obj.get("f_name") == null) ? "" : obj.get("f_name").toString().trim();
				    	String l_name = (obj.get("l_name") == null) ? "" : obj.get("l_name").toString().trim();
				    	String ic = (obj.get("ic") == null) ? "" : obj.get("ic").toString().trim();
				    	
				    	if(k == 0){
					    	owner_name_1 = f_name + " " + l_name;
					    	owner_id_1 = ic;
				    	}
				    	else if(k == 1){
				    		owner_name_2 = f_name + " " + l_name;
					    	owner_id_2 = ic;
				    	}
				    	else if(k == 2){
				    		owner_name_3 = f_name + " " + l_name;
					    	owner_id_3 = ic;
				    	}
				    	else {
				    		break;
				    	}
					}
				}
				
				
				HSSFRow thisRow = sheet.getRow(nextRow);
				if(thisRow == null) {
					thisRow = sheet.createRow(nextRow);
				}
				
				HSSFCellStyle myStyle = workbook.createCellStyle();
				
				thisRow.getCell(0, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue("T"); thisRow.getCell(0).setCellStyle(myStyle);
				thisRow.getCell(1, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue("S"); thisRow.getCell(1).setCellStyle(myStyle);
				thisRow.getCell(2, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(1); thisRow.getCell(2).setCellStyle(myStyle);
				
				thisRow.getCell(4, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(c.get("registered_name")); thisRow.getCell(4).setCellStyle(myStyle);
				thisRow.getCell(5, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue("Internet Payment"); thisRow.getCell(5).setCellStyle(myStyle);
				
				if(c.get("registered_no").equals("") == false) {
					thisRow.getCell(6, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue("000" + c.get("registered_no")); thisRow.getCell(6).setCellStyle(myStyle);
				}
				
				thisRow.getCell(7, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(line_1); thisRow.getCell(7).setCellStyle(myStyle);
				thisRow.getCell(8, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(city); thisRow.getCell(8).setCellStyle(myStyle);
				thisRow.getCell(9, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(region_code); thisRow.getCell(9).setCellStyle(myStyle);
				thisRow.getCell(10, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(postal_code); thisRow.getCell(10).setCellStyle(myStyle);
				thisRow.getCell(11, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(merchant_country_code); thisRow.getCell(11).setCellStyle(myStyle);
				thisRow.getCell(12, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(c.get("email")); thisRow.getCell(12).setCellStyle(myStyle);
				thisRow.getCell(13, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue("MOTO"); thisRow.getCell(13).setCellStyle(myStyle);
				thisRow.getCell(14, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(acquirer_country_code); thisRow.getCell(14).setCellStyle(myStyle);
				thisRow.getCell(15, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(c.get("mcc_code")); thisRow.getCell(15).setCellStyle(myStyle);
				
				thisRow.getCell(22, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(contract_open_date); thisRow.getCell(22).setCellStyle(myStyle);
				thisRow.getCell(23, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(contract_close_date); thisRow.getCell(23).setCellStyle(myStyle);
				thisRow.getCell(24, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(c.get("tel_no")); thisRow.getCell(24).setCellStyle(myStyle);
				
				thisRow.getCell(43, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_name_1); thisRow.getCell(43).setCellStyle(myStyle);
				thisRow.getCell(44, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_id_1); thisRow.getCell(44).setCellStyle(myStyle);
				
				thisRow.getCell(49, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_name_2); thisRow.getCell(49).setCellStyle(myStyle);
				thisRow.getCell(50, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_id_2); thisRow.getCell(50).setCellStyle(myStyle);
				
				thisRow.getCell(55, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_name_3); thisRow.getCell(55).setCellStyle(myStyle);
				thisRow.getCell(56, HSSFRow.CREATE_NULL_AS_BLANK).setCellValue(owner_id_3); thisRow.getCell(56).setCellStyle(myStyle);
				
				++nextRow;
			}
		
		} finally {
			fileIn.close();
		}
		
		
		FileOutputStream fileOut = new FileOutputStream(new File(exportFileName));
		
		try {
			workbook.write(fileOut);
			workbook.close();
		} finally {
			fileOut.close();
		}
		
		
		File fileToDownload = new File(exportFileName);
		fileIn = new FileInputStream(fileToDownload);
		
		ServletOutputStream outStream = response.getOutputStream();
		
		response.setContentType("application/octet-stream");
		response.setContentLength((int) fileToDownload.length());
		response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
		
		byte[] buffer = new byte[4096];
		int bytesRead = -1;
		 
		while ((bytesRead = fileIn.read(buffer)) != -1) {
			outStream.write(buffer, 0, bytesRead);
		}
		
		fileIn.close();
		outStream.close();
	}
}
